package kr.ac.kaist.ysproject.util;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Random;

import kr.ac.kaist.ysproject.data.DataMsgHistory;
import kr.ac.kaist.ysproject.data.DataProfile;
import kr.ac.kaist.ysproject.data.DataSmartMsgInfo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbManager {
	private static final String DATABASE_NAME = "lovemanager.db";
	private static final int	DATABASE_VERSION = 12;
	private static DbManager	m_instance = null;
	private DatabaseHelper		m_dbHelper = null;
	private SQLiteDatabase 		m_db = null;

	private DbManager() {
	}

	private DbManager(Context context) {
		m_dbHelper = new DatabaseHelper(context);
		m_db = m_dbHelper.getWritableDatabase();
	}

	public static DbManager getInstance(Context context) {
		if (m_instance == null) {
			m_instance = new DbManager(context);
		}

		return m_instance;
	}

	private class DatabaseHelper extends SQLiteOpenHelper{
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		public void onCreate(SQLiteDatabase db){
			// TB_PROFILE
			db.execSQL("CREATE TABLE IF NOT EXISTS TB_PROFILE ( " +
					"ID INTEGER, " +
					"NAME TEXT, " +
					"MOBILE_NO TEXT, " +
					"FACEBOOK_ID TEXT, " +
					"EMAIL TEXT, " +
					"HOME_ADDR TEXT, " +
					"BIRTH TEXT, " +
					"BLOOD_TYPE TEXT, " +
					"FAVOR_MUSIC TEXT, " +
					"FAVOR_MOVIE TEXT, " +
					"FAVOR_TV TEXT, " +
					"FAVOR_BOOK TEXT, " +
					"FAVOR_GAME TEXT, " +
					"FAVOR_PEOPLE TEXT, " +
					"FAVOR_SPORTS TEXT, " +
					"FAVOR_HOBBY TEXT, " +
					"FAVOR_DISLIKE TEXT, " +
					"RATE_APPEARANCE TEXT, " +
					"RATE_BODYSHAPE TEXT, " +
					"RATE_MONEY TEXT, " +
					"RATE_CHARACTER TEXT, " +
					"RATE_ACADEMIC TEXT, " +
					"RATE_HOUSEHOLD TEXT, " +
					"RATE_TOTAL TEXT, " +
					"PICTURE_PATH TEXT, " +
					"PRIMARY KEY(ID) " +
			");");			

			// TB_HISTORY
			db.execSQL("CREATE TABLE IF NOT EXISTS TB_HISTORY ( " +
					"ID INTEGER, " +
					"SENDER TEXT, " +
					"DATE TEXT, " +
					"PRIMARY KEY(ID) " +
			");");

			// TB_SMARTMSGINFO
			db.execSQL("CREATE TABLE IF NOT EXISTS TB_SMARTMSGINFO ( " +
					"ID INTEGER, " +
					"NAME TEXT, " +
					"TIME TEXT, " +
					"TIME_DEV TEXT, " +
					"DATE TEXT, " +
					"SEND_TYPE TEXT, " +
					"MANUAL_MSG TEXT, " +
					"INTERNAL_MSG_USE TEXT, " +
					"WEEKLY_REPEAT TEXT, " +
					"CATEGORY TEXT, " +
					"RESPECT TEXT, " +
					"TALKING_WAY TEXT, " +
					"PROF_ID INTEGER, " +
					"NEXT_SEND_TIME TEXT, " +
					"PRIMARY KEY(ID) " +
			");");

			// TB_INTERNALMSG
			db.execSQL("CREATE TABLE IF NOT EXISTS TB_INTERNALMSG ( " +
					"ID INTEGER, " +
					"MSG TEXT, " +
					"CATEGORY TEXT, " +
					"RESPECT TEXT, " +
					"TALKING_WAY TEXT, " +
					"PRIMARY KEY(ID) " +
			");");

			// 모닝콜/존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침이에요^^', '모닝콜', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침이에요 일어났어요?^^', '모닝콜', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('일어날 시간이에요~', '모닝콜', '사용', '일반')");
			
			// 모닝콜/비존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침^^', '모닝콜', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침~ 일어났어?^^', '모닝콜', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('일어날 시간이야~', '모닝콜', '사용안함', '일반')");
			
			// 모닝콜/존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침이에염^^', '모닝콜', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침이에염~ 인났어요?^^', '모닝콜', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('인날 시간이에염~', '모닝콜', '사용', '귀여움')");
			
			// 모닝콜/비존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침이얌^^', '모닝콜', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 아침~ 인났엉?^^', '모닝콜', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('인날 시간이얌~', '모닝콜', '사용안함', '귀여움')");
			
			
			// 식사/존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('식사하셨어요?', '식사', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('식사는 하셨는지 모르겠네요ㅎ', '식사', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 뭐 먹었어요?ㅎ', '식사', '사용', '일반')");
			
			// 식사/비존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥먹었어?', '식사', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥은 먹었는지 모르겠네ㅎ', '식사', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 뭐 먹었어?ㅎ', '식사', '사용안함', '일반')");
			
			// 식사/존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('식사하셨어염?', '식사', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('식사는 하셨는지 모르겠네염ㅎ', '식사', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 뭐 먹었어용?ㅋ', '식사', '사용', '귀여움')");
			
			// 식사/비존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥먹었엉?', '식사', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥은 쫌 먹었는지 모르겠넹ㅎ', '식사', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 뭐 먹었엉?ㅋ', '식사', '사용안함', '귀여움')");

			
			// 사랑표현/존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘도 사랑해요~', '사랑표현', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('아~ 보고싶어요~', '사랑표현', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('나 보고 싶지 않아요?', '사랑표현', '사용', '일반')");
			
			// 사랑표현/비존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘도 사랑해~', '사랑표현', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('아~ 보고싶어!', '사랑표현', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('나 보고 싶지 않아?', '사랑표현', '사용안함', '일반')");
			
			// 사랑표현/존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘두 사랑해요^^', '사랑표현', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('아앙~ 보고싶어용^^', '사랑표현', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('나 보고 싶지 않아염^^?', '사랑표현', '사용', '귀여움')");
			
			// 사랑표현/비존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘두 싸랑해^^ㅋㅋ', '사랑표현', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('아앙~ 보고싶엉~~!^^', '사랑표현', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('나 안보고 싶엉^^?ㅋ', '사랑표현', '사용안함', '귀여움')");

			
			// 안부/존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘 지내고 계시죠?', '안부', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('어떻게 지내세요?', '안부', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥은 잘 챙겨먹고 다니는거죠?', '안부', '사용', '일반')");
			
			// 안부/비존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘 지내니?', '안부', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('어떻게 지내니?', '안부', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥은 잘 챙겨먹고 있지?', '안부', '사용안함', '일반')");
			
			// 안부/존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘 지내고 계세용?ㅋ', '안부', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('어떻게 지내용?', '안부', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥은 잘 챙겨먹구 있는 거죵?ㅎ', '안부', '사용', '귀여움')");
			
			// 안부/비존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘 지내?ㅋㅋ', '안부', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('어떻게 지내?ㅋㅋ', '안부', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('밥은 잘 챙겨먹구 댕기니?ㅋㅋ', '안부', '사용안함', '귀여움')");
			
			
			
			// 굿나잇/존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘 주무세요~', '굿나잇', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 꿈 꾸세요~', '굿나잇', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('굿 나잇~~', '굿나잇', '사용', '일반')");
			
			// 굿나잇/비존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘자~', '굿나잇', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 꿈 꿔~', '굿나잇', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('굿 나잇~ㅋㅋ', '굿나잇', '사용안함', '일반')");
			
			// 굿나잇/존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘자용~', '굿나잇', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 꿈 꾸세용~^^ㅋ', '굿나잇', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('굿나잇^^~~', '굿나잇', '사용', '귀여움')");
			
			// 굿나잇/비존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘자~!!ㅋㅋ', '굿나잇', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('좋은 꿈꿔~~^^ㅋㅋ', '굿나잇', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('굿나잇^^~~ㅋㅋ', '굿나잇', '사용안함', '귀여움')");
			
			
			// 귀가/존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('조심히 들어가세요~', '귀가', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('집에 일찍 들어가서 쉬세요~', '귀가', '사용', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 하루 수고했어요~', '귀가', '사용', '일반')");
			
			// 귀가/비존칭/일반
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘 들어가~', '귀가', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('집에 일찍 들어가서 쉬어~', '귀가', '사용안함', '일반')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 하루 수고했어~', '귀가', '사용안함', '일반')");
			
			// 귀가/존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('조심히 들어가염~~', '귀가', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('집에 일찍 둘가서 쉬세용^^', '귀가', '사용', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 하루 수고했어용^^', '귀가', '사용', '귀여움')");
			
			// 귀가/비존칭/귀염
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('잘 들어가~~!^^', '귀가', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('집에 일찍 둘가서 쉬어~~!^^', '귀가', '사용안함', '귀여움')");
			
			db.execSQL("INSERT INTO TB_INTERNALMSG (MSG, CATEGORY, RESPECT, TALKING_WAY) " + 
			"VALUES ('오늘 하루 수고했엉~~^^ㅋ', '귀가', '사용안함', '귀여움')");
			

			// TB_SETTING
			db.execSQL("CREATE TABLE IF NOT EXISTS TB_SETTING ( " +
					"STOP_ALL_SENDING TEXT, " +
					"MY_FACEBOOK_ID TEXT " +
			");");

			db.execSQL("INSERT INTO TB_SETTING VALUES ('0', '')");
			
			// TB_MSGHISTORY
			db.execSQL("CREATE TABLE IF NOT EXISTS TB_MSGHISTORY ( " +
				      "ID INTEGER, " +
				      "SENT_DATE, " +
				      "MSG TEXT, " +
				      "PROF_ID INTEGER, " +
				      "PRIMARY KEY(ID) " +
				");");
		}

		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
			db.execSQL("DROP TABLE IF EXISTS TB_PROFILE");
			db.execSQL("DROP TABLE IF EXISTS TB_HISTORY");
			db.execSQL("DROP TABLE IF EXISTS TB_SMARTMSGINFO");
			db.execSQL("DROP TABLE IF EXISTS TB_INTERNALMSG");
			db.execSQL("DROP TABLE IF EXISTS TB_SETTING");

			onCreate(db);
		}
	}

	public ArrayList<DataSmartMsgInfo> getSmartMsgInfo(String profileId) {
		ArrayList<DataSmartMsgInfo> arrResult = new ArrayList<DataSmartMsgInfo>();

		try {
			Cursor cur = m_db.query("TB_SMARTMSGINFO", 
					new String[] {"ID", "NAME", "TIME", "TIME_DEV", "DATE", "SEND_TYPE", 
					"MANUAL_MSG", "INTERNAL_MSG_USE", "WEEKLY_REPEAT",
					"CATEGORY", "RESPECT", "TALKING_WAY", "NEXT_SEND_TIME"}, 
					"PROF_ID = ?", new String[] {profileId}, null, null, null);

			int numRows = cur.getCount();

			if (numRows > 0) {

				cur.moveToFirst();

				for (int i = 0; i < numRows; i++) {
					DataSmartMsgInfo msgInfo = new DataSmartMsgInfo();

					msgInfo.setId(cur.getInt(0));
					msgInfo.setName(cur.getString(1));
					msgInfo.setTime(cur.getString(2));
					msgInfo.setTimeDev(cur.getString(3));
					msgInfo.setDate(cur.getString(4));
					msgInfo.setSendType(cur.getString(5));
					msgInfo.setManualMsg(cur.getString(6));
					msgInfo.setInternalMsgUse(cur.getString(7));
					msgInfo.setWeeklyRepeat(cur.getString(8));
					msgInfo.setCategory(cur.getString(9));
					msgInfo.setRespect(cur.getString(10));
					msgInfo.setTalkingWay(cur.getString(11));
					msgInfo.setProfId(profileId);
					msgInfo.setNextSendTime(cur.getString(12));

					arrResult.add(msgInfo);

					cur.moveToNext();
				}
			}
			
			cur.close();

		} catch (SQLException e) {
			Log.e("sqlerr", e.toString());
		}

		return arrResult;
	}

	public void saveSmartMsgInfo(DataSmartMsgInfo smsgInfo) {
		ContentValues args = new ContentValues();

		args.put("NAME", smsgInfo.getName());
		args.put("TIME", smsgInfo.getTime());
		args.put("TIME_DEV", smsgInfo.getTimeDev());
		args.put("DATE", smsgInfo.getDate());
		args.put("SEND_TYPE", smsgInfo.getSendType());
		args.put("MANUAL_MSG", smsgInfo.getManualMsg());
		args.put("INTERNAL_MSG_USE", smsgInfo.getInternalMsgUse());
		args.put("WEEKLY_REPEAT", smsgInfo.getWeeklyRepeat());
		args.put("CATEGORY", smsgInfo.getCategory());
		args.put("RESPECT", smsgInfo.getRespect());
		args.put("TALKING_WAY", smsgInfo.getTalkingWay());
		args.put("PROF_ID", Integer.parseInt(smsgInfo.getProfId()));
		args.put("NEXT_SEND_TIME", smsgInfo.newNextSendTime());

		if (smsgInfo.getId() > -1) {
			// update
			m_db.update("TB_SMARTMSGINFO", args, "id=" + smsgInfo.getId(), null);
		} else {
			// insert
			m_db.insert("TB_SMARTMSGINFO", null, args);
		}
	}
	
	public ArrayList<DataSmartMsgInfo> getSmartMsgInfoForAlarm() {
		
		Calendar calendar = Calendar.getInstance();
		Log.d("DbManager", "hour = " + calendar.get(Calendar.HOUR_OF_DAY) + "min = " + calendar.get(Calendar.MINUTE));
		
		int	curHour = calendar.get(Calendar.HOUR_OF_DAY);
		int curMin = calendar.get(Calendar.MINUTE);
		
		//int befHour = (curHour * 60 + curMin - 30) / 60;
		//int befMin = (curHour * 60 + curMin - 30) % 60;
		//int aftHour = (curHour * 60 + curMin + 30) / 60;
		//int aftMin = (curHour * 60 + curMin + 30) % 60;
		
		String curTime = new String().format("%02d:%02d", curHour, curMin);
		//String aftTime = new String().format("%02d:%02d", aftHour, aftMin);
		
		Log.d("DbManager", "curTime = " + curTime);
		
		ArrayList<DataSmartMsgInfo> arrResult = new ArrayList<DataSmartMsgInfo>();
		
		try {
			Cursor cur = m_db.query("TB_SMARTMSGINFO", 
					new String[] {"ID", "NAME", "TIME", "TIME_DEV", "DATE", "SEND_TYPE", 
					"MANUAL_MSG", "INTERNAL_MSG_USE", "WEEKLY_REPEAT",
					"CATEGORY", "RESPECT", "TALKING_WAY", "PROF_ID", "NEXT_SEND_TIME"}, 
					"NEXT_SEND_TIME = ? AND SEND_TYPE <> ?", new String[] {curTime, "중지"}, null, null, null);

			int numRows = cur.getCount();
			
			Log.d("DbManager", "numRows = " + numRows);

			if (numRows > 0) {

				cur.moveToFirst();

				for (int i = 0; i < numRows; i++) {
					boolean bValid = false;
					
					DataSmartMsgInfo msgInfo = new DataSmartMsgInfo();

					msgInfo.setId(cur.getInt(0));
					msgInfo.setName(cur.getString(1));
					msgInfo.setTime(cur.getString(2));
					msgInfo.setTimeDev(cur.getString(3));
					msgInfo.setDate(cur.getString(4));
					msgInfo.setSendType(cur.getString(5));
					msgInfo.setManualMsg(cur.getString(6));
					msgInfo.setInternalMsgUse(cur.getString(7));
					msgInfo.setWeeklyRepeat(cur.getString(8));
					msgInfo.setCategory(cur.getString(9));
					msgInfo.setRespect(cur.getString(10));
					msgInfo.setTalkingWay(cur.getString(11));
					msgInfo.setProfId(Integer.toString(cur.getInt(12)));
					msgInfo.setNextSendTime(cur.getString(13));
					
					if (!msgInfo.getDate().equals("")) {
						// 일자설정이 되어 있는 경우 현재일과 동일일자인지 체크
						String curDate = new String().format("%04d.%02d.%02d"
								, calendar.get(Calendar.YEAR)
								, calendar.get(Calendar.MONTH) + 1
								, calendar.get(Calendar.DAY_OF_MONTH));
						
						if (msgInfo.getDate().equals(curDate)) {
							bValid = true;
						}
					} else {
						// 일자설정이 없는 경우 주간반복에 현재요일이 포함되어있는지 체크
						String strCurDayOfWeek = "none";
						
						if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) {
							strCurDayOfWeek = "일";
						} else if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.MONDAY) {
							strCurDayOfWeek = "월";
						} else if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.TUESDAY) {
							strCurDayOfWeek = "화";
						} else if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.WEDNESDAY) {
							strCurDayOfWeek = "수";
						} else if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.THURSDAY) {
							strCurDayOfWeek = "목";
						} else if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.FRIDAY) {
							strCurDayOfWeek = "금";
						} else if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY) {
							strCurDayOfWeek = "토";
						}
						
						Log.d("DbManager", "strCurDayOfWeek = " + strCurDayOfWeek);
						
						if (msgInfo.getWeeklyRepeat().contains(strCurDayOfWeek)) {
							bValid = true;
							Log.d("DbManager", "요일 포함");
						}
					}

					if (bValid) {
						arrResult.add(msgInfo);
					}
					
					cur.moveToNext();
				}		
			}
			
			cur.close();

		} catch (SQLException e) {
			Log.e("sqlerr", e.toString());
		}
		
		return arrResult;
	}
	
	public ArrayList<DataProfile> getAllProfile() {
		ArrayList<DataProfile> arrResult = new ArrayList<DataProfile>();

		try {
			Cursor cur = m_db.query("TB_PROFILE", 
					new String[] {"ID", 
					"NAME",
					"MOBILE_NO",
					"FACEBOOK_ID",
					"EMAIL",
					"HOME_ADDR",
					"BIRTH",
					"BLOOD_TYPE",
					"FAVOR_MUSIC",
					"FAVOR_MOVIE",
					"FAVOR_TV",
					"FAVOR_BOOK",
					"FAVOR_GAME",
					"FAVOR_PEOPLE",
					"FAVOR_SPORTS",
					"FAVOR_HOBBY",
					"FAVOR_DISLIKE",
					"RATE_APPEARANCE",
					"RATE_BODYSHAPE",
					"RATE_MONEY",
					"RATE_CHARACTER",
					"RATE_ACADEMIC",
					"RATE_HOUSEHOLD",
					"RATE_TOTAL",
					"PICTURE_PATH"}, 
					null, null, null, null, null);

			int numRows = cur.getCount();

			if (numRows > 0) {

				cur.moveToFirst();

				for (int i = 0; i < numRows; i++) {
					DataProfile profile = new DataProfile();

					profile.setId(cur.getInt(0));
					profile.setName(cur.getString(1));
					profile.setMobileNo(cur.getString(2));
					profile.setFacebookId(cur.getString(3));
					profile.setEmail(cur.getString(4));
					profile.setHomeAddr(cur.getString(5));
					profile.setBirth(cur.getString(6));
					profile.setBloodType(cur.getString(7));
					profile.setFavorMusic(cur.getString(8));
					profile.setFavorMovie(cur.getString(9));
					profile.setFavorTv(cur.getString(10));
					profile.setFavorBook(cur.getString(11));
					profile.setFavorGame(cur.getString(12));
					
					profile.setFavorPeople(cur.getString(13));
					profile.setFavorSports(cur.getString(14));
					profile.setFavorHobby(cur.getString(15));
					profile.setFavorDislike(cur.getString(16));
					profile.setRateAppearance(cur.getString(17));
					profile.setRateBodyShape(cur.getString(18));
					profile.setRateMoney(cur.getString(19));
					profile.setRateCharacter(cur.getString(20));
					profile.setRateAcademic(cur.getString(21));
					profile.setRateHousehold(cur.getString(22));
					profile.setRateTotal(cur.getString(23));
					
					profile.setPicturePath(cur.getString(24));

					arrResult.add(profile);

					cur.moveToNext();
				}

			}
		
			cur.close();

		} catch (SQLException e) {
			Log.e("sqlerr", e.toString());
		}

		return arrResult;
	}
	
	public DataProfile getProfileById(String profId) {
		DataProfile profile = null;

		Log.d("DbManager", "getProfileById : profId = " + profId);
		try {
			Cursor cur = m_db.query("TB_PROFILE", 
					new String[] {"ID", 
					"NAME",
					"MOBILE_NO",
					"FACEBOOK_ID",
					"EMAIL",
					"HOME_ADDR",
					"BIRTH",
					"BLOOD_TYPE",
					"FAVOR_MUSIC",
					"FAVOR_MOVIE",
					"FAVOR_TV",
					"FAVOR_BOOK",
					"FAVOR_GAME",
					"FAVOR_PEOPLE",
					"FAVOR_SPORTS",
					"FAVOR_HOBBY",
					"FAVOR_DISLIKE",
					"RATE_APPEARANCE",
					"RATE_BODYSHAPE",
					"RATE_MONEY",
					"RATE_CHARACTER",
					"RATE_ACADEMIC",
					"RATE_HOUSEHOLD",
					"RATE_TOTAL",
					"PICTURE_PATH"}, 
					"ID = ?", new String[] {profId}, null, null, null);

			int numRows = cur.getCount();

			if (numRows > 0) {

				cur.moveToFirst();

				profile = new DataProfile();

				profile.setId(cur.getInt(0));
				profile.setName(cur.getString(1));
				profile.setMobileNo(cur.getString(2));
				profile.setFacebookId(cur.getString(3));
				profile.setEmail(cur.getString(4));
				profile.setHomeAddr(cur.getString(5));
				profile.setBirth(cur.getString(6));
				profile.setBloodType(cur.getString(7));
				profile.setFavorMusic(cur.getString(8));
				profile.setFavorMovie(cur.getString(9));
				profile.setFavorTv(cur.getString(10));
				profile.setFavorBook(cur.getString(11));
				profile.setFavorGame(cur.getString(12));

				profile.setFavorPeople(cur.getString(13));
				profile.setFavorSports(cur.getString(14));
				profile.setFavorHobby(cur.getString(15));
				profile.setFavorDislike(cur.getString(16));
				profile.setRateAppearance(cur.getString(17));
				profile.setRateBodyShape(cur.getString(18));
				profile.setRateMoney(cur.getString(19));
				profile.setRateCharacter(cur.getString(20));
				profile.setRateAcademic(cur.getString(21));
				profile.setRateHousehold(cur.getString(22));
				profile.setRateTotal(cur.getString(23));
				
				profile.setPicturePath(cur.getString(24));

			}
		
			cur.close();

		} catch (SQLException e) {
			Log.e("sqlerr", e.toString());
		}

		return profile;
	}

	public void saveProfile(DataProfile profile) {
		ContentValues args = new ContentValues();

		args.put("NAME", profile.getName());
		args.put("MOBILE_NO", profile.getMobileNo());
		args.put("FACEBOOK_ID", profile.getFacebookId());
		args.put("EMAIL", profile.getEmail());
		args.put("HOME_ADDR", profile.getHomeAddr());
		args.put("BIRTH", profile.getBirth());
		args.put("BLOOD_TYPE", profile.getBloodType());
		args.put("FAVOR_MUSIC", profile.getFavorMusic());
		args.put("FAVOR_MOVIE", profile.getFavorMovie());
		args.put("FAVOR_TV", profile.getFavorTv());
		args.put("FAVOR_BOOK", profile.getFavorBook());
		args.put("FAVOR_GAME", profile.getFavorGame());
		args.put("FAVOR_PEOPLE", profile.getFavorPeople());
		args.put("FAVOR_SPORTS", profile.getFavorSports());
		args.put("FAVOR_HOBBY", profile.getFavorHobby());
		args.put("FAVOR_DISLIKE", profile.getFavorDislike());
		args.put("RATE_APPEARANCE", profile.getRateAppearance());
		args.put("RATE_BODYSHAPE", profile.getRateBodyShape());
		args.put("RATE_MONEY", profile.getRateMoney());
		args.put("RATE_CHARACTER", profile.getRateCharacter());
		args.put("RATE_ACADEMIC", profile.getRateAcademic());
		args.put("RATE_HOUSEHOLD", profile.getRateHousehold());
		args.put("RATE_TOTAL", profile.getRateTotal());
		args.put("PICTURE_PATH", profile.getPicturePath());

		if (profile.getId() > -1) {
			// update
			m_db.update("TB_PROFILE", args, "id=" + profile.getId(), null);
		} else {
			// insert
			m_db.insert("TB_PROFILE", null, args);
		}
	}
	
	public String getInternalMsgByRand(String strCategory, String strRespect, String strTalkingWay) {
		String msg = null;

		try {
			Cursor cur = m_db.query("TB_INTERNALMSG", 
					new String[] {"MSG"}, 
					"CATEGORY = ? AND RESPECT = ? AND TALKING_WAY = ?", 
					new String[] {strCategory, strRespect, strTalkingWay}, null, null, null);

			int numRows = cur.getCount();

			if (numRows > 0) {
				Random rand = new Random();
				int randVal = rand.nextInt(numRows);
				
				Log.d("DbManager", "getInternalMsgByRand:numRows = " + numRows);
				Log.d("DbManager", "getInternalMsgByRand:randVal = " + randVal);

				cur.moveToFirst();
				
				for (int i = 0; i < randVal; i++) {
					cur.moveToNext();
				}

				msg = cur.getString(0);
			}
		
			cur.close();

		} catch (SQLException e) {
			Log.e("sqlerr", e.toString());
		}

		return msg;
	}
	
	public ArrayList<DataMsgHistory> getMsgHistory(String profileId) {
		ArrayList<DataMsgHistory> arrResult = new ArrayList<DataMsgHistory>();

		try {
			Cursor cur = m_db.query("TB_MSGHISTORY", 
					new String[] {"ID", "SENT_DATE", "MSG", "PROF_ID"}, 
					"PROF_ID = ?", new String[] {profileId}, null, null, "SENT_DATE DESC");

			int numRows = cur.getCount();

			if (numRows > 0) {

				cur.moveToFirst();

				for (int i = 0; i < numRows; i++) {
					DataMsgHistory msgInfo = new DataMsgHistory();

					msgInfo.setId(cur.getInt(0));
					msgInfo.setSentDate(cur.getString(1));
					msgInfo.setMsg(cur.getString(2));
					msgInfo.setProfId(Integer.toString(cur.getInt(3)));

					arrResult.add(msgInfo);

					cur.moveToNext();
				}
			}
			
			cur.close();

		} catch (SQLException e) {
			Log.e("sqlerr", e.toString());
		}

		return arrResult;
	}

	public void saveMsgHistory(DataMsgHistory smsgInfo) {
		ContentValues args = new ContentValues();

		args.put("SENT_DATE", smsgInfo.getSentDate());
		args.put("MSG", smsgInfo.getMsg());
		args.put("PROF_ID", smsgInfo.getProfId());

		if (smsgInfo.getId() > -1) {
			// update
			m_db.update("TB_MSGHISTORY", args, "id=" + smsgInfo.getId(), null);
		} else {
			// insert
			m_db.insert("TB_MSGHISTORY", null, args);
		}
	}
}
